#!/usr/bin/env python
#-*- coding:utf-8 -*-
from wwpy.io.Source import Source
from wwpy.util.Threads import ThreadPool

class AdAttr(object):
    
    def __init__(self,path='.'):
        self.sep='|'
        self.ad_attr_file='%s/ad_attr_all' % (path)
        self.table='ad_attr_all'
    
    def _get_info(self,sql):
        info={}
        s=Source(sql)
        for row in s.read():
            key,values=row[0],row[1:]
            info[key]=values
        return info
    
    def ad_info(self):
        sql="select subscribe_id,user_id,iswholeput,bid,budget,auction_type,state,ad_source,add_time,mod_time "
        sql+="from db_bid_promotion.subscribe where ad_source in (1,3) and auction_type=1"
        return self._get_info(sql)
    
    def creative_info(self):
        tp=ThreadPool()
        for idx in range(8):
            sql="select subscribe_id,creative_id,entity_id from db_bid_promotion.creative_info_%s where entity_type=0 and auction_type=1 " % (idx)
            tp.put(self._get_info,sql)
        tp.wait()
        info={}
        for out in tp.outs():
            info.update(out[-1])
        return info
    
    def post_info(self):
        sql="select info_id,display_city1,display_city2,display_city3,display_cate1,display_cate2,display_cate3,display_cate4,state from db_precise_ib.info_attr "
        return self._get_info(sql)
    
    def handle(self):
        ai=self.ad_info()
        ci=self.creative_info()
        pi=self.post_info()
        f=open(self.ad_attr_file,'w')
        for sid in ai:
            ad_attr=ai[sid]
            if sid not in ci:
                continue
            cid,info_id=ci[sid]
            info_attr=pi.get(info_id,map(lambda idx:'',range(7))+[0])
            f.write(self.sep.join(str(field) for field in [sid,cid,info_id]+ad_attr+info_attr)+'\n')
        f.close()
        
    def load(self):
        table_fields=[
            "ad_id bigint(20) comment '广告id'",
            "creative_id bigint(20) comment '创意id'",
            "info_id bigint(20) comment '帖子id'",
            "user_id bigint(20) comment '用户id'",
            "is_whole int(10) comment '是否全网投放 0:否 1:是 精准系统默认:0'",
            "bid double comment '竞价 分'",
            "budget double comment '预算金额'",
            "auction_type tinyint(4) comment '竞价类型 0:cpt 1:cpc 精准推广默认:0 智能推广默认:0'",
            "ad_state tinyint(4) comment '广告状态 -1:新建 0:正常 1:搁置 2:删除'",
            "ad_source tinyint(4) comment '广告类型 1:精准 2:智能 3:精准&智能'",
            "add_time datetime comment '增加时间'",
            "mod_time datetime comment '修改时间'",
            "city1 varchar(30) comment '一级城市'",
            "city2 varchar(30) comment '二级城市'",
            "city3 varchar(30) comment '三级城市'",
            "cate1 varchar(30) comment '一级类别'",
            "cate2 varchar(30) comment '二级类别'",
            "cate3 varchar(30) comment '三级类别'",
            "cate4 varchar(30) comment '四级类别'",
            "info_state int(10) comment '帖子状态'",
            "primary key (`ad_id`)",
        ]
        from wwpy.db.DBI import DBI
        dbi=DBI(dbc='db_pre_ads')
        dbi.save(self.ad_attr_file,self.table,table_fields,sep=self.sep,is_drop=True)
        dbi.close()
        
def main():
    ad=AdAttr('/work2/opt/analytic/precise/ad_attr')
    ad.handle()
    ad.load()
    
if __name__ == '__main__':
    main()
    
